#!/bin/bash
# 作用：DIM层，每日数据装载（ODS -> DIM）

# 1.判断参数个数
if [ $# -lt 1 ]
then
        echo "请输入正确的参数表名/all" && exit
fi

# 2.定义时间属性
[ "$2" ] && dateStr=$2 || dateStr=`date -d '-1 day' +%F`

dim_activity_full="with
ai as (
    select id,
           activity_name,
           activity_type,
           activity_desc,
           start_time,
           end_time,
           create_time
    from ods_activity_info_full
    where dt='${dateStr}'
),ar as (
    select id,
           activity_id,
           condition_amount,
           condition_num,
           benefit_amount,
           benefit_discount,
           case activity_type
            when '3101' then concat('满',condition_amount,'元减',benefit_amount,'元')
            when '3102' then concat('满',condition_num,'件打',(1 - benefit_discount) * 10,'折')
            when '3103' then concat('打',(1 - benefit_discount) * 10,'折')
           end benefit_rule,
           benefit_level
    from ods_activity_rule_full
    where dt='${dateStr}'
),dic31 as (
    select dic_code,dic_name
    from ods_base_dic_full
    where dt='${dateStr}' and parent_code='31'
)
insert overwrite table dim_activity_full partition (dt='${dateStr}')
select ar.id,
       ai.id,
       ai.activity_name,
       ai.activity_type,
       dic31.dic_name,
       ai.activity_desc,
       start_time,
       end_time,
       create_time,
       condition_amount,
       condition_num,
       benefit_amount,
       benefit_discount,
       benefit_rule,
       benefit_level
from ai
join ar on ai.id = ar.activity_id
join dic31 on ai.activity_type = dic31.dic_code;"
dim_coupon_full="with
cou as (
    select id,
           coupon_name,
           coupon_type,
           condition_amount,
           condition_num,
           activity_id,
           benefit_amount,
           benefit_discount,
           case coupon_type
            when '3201' then concat('满',condition_amount,'元减',benefit_amount,'元')
            when '3202' then concat('满',condition_num,'件打',(1 - benefit_discount) * 10,'折')
            when '3203' then concat('满',condition_amount,'元减',benefit_amount,'元')
           end benefit_rule,
           create_time,
           range_type,
           limit_num,
           taken_count,
           start_time,
           end_time,
           operate_time,
           expire_time
    from ods_coupon_info_full
    where dt='${dateStr}'
),dic32 as (
    select dic_code,dic_name
    from ods_base_dic_full
    where dt='${dateStr}' and parent_code='32'
),dic33 as (
    select dic_code,dic_name
    from ods_base_dic_full
    where dt='${dateStr}' and parent_code='33'
)
insert overwrite table dim_coupon_full partition (dt='${dateStr}')
select id,
       coupon_name,
       coupon_type,
       dic32.dic_name coupon_type_name,
       condition_amount,
       condition_num,
       activity_id,
       benefit_amount,
       benefit_discount,
       benefit_rule,
       create_time,
       range_type,
       dic33.dic_name range_type_name,
       limit_num,
       taken_count,
       start_time,
       end_time,
       operate_time,
       expire_time
from cou
join dic32 on cou.coupon_type = dic32.dic_code
join dic33 on cou.range_type = dic33.dic_code;"
dim_province_full="with
p as (
    select id,
           name,
           area_code,
           iso_code,
           iso_3166_2,
           region_id
    from ods_base_province_full
    where dt='${dateStr}'
),r as (
    select id,
           region_name
    from ods_base_region_full
    where dt='${dateStr}'
)
insert overwrite table dim_province_full partition (dt='${dateStr}')
select p.id,
       name,
       area_code,
       iso_code,
       iso_3166_2,
       region_id,
       region_name
from p
left join r on p.region_id = r.id;"
dim_sku_full="with
sku as (
    select id,
           price,
           sku_name,
           sku_desc,
           weight,
           is_sale,
           spu_id,
           tm_id,
           category3_id,
           create_time
    from ods_sku_info_full
    where dt='${dateStr}'
),spu as (
    select id,
           spu_name
    from ods_spu_info_full
    where dt='${dateStr}'
),c3 as (
    select id,
           name,
           category2_id
    from ods_base_category3_full
    where dt='${dateStr}'
),c2 as (
    select id,
           name,
           category1_id
    from ods_base_category2_full
    where dt='${dateStr}'
),c1 as (
    select id,
           name
    from ods_base_category1_full
    where dt='${dateStr}'
),tm as (
    select id,
           tm_name
    from ods_base_trademark_full
    where dt='${dateStr}'
),attr as (
    select sku_id,
           collect_list(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attr_values
    from ods_sku_attr_value_full
    where dt='${dateStr}'
    group by sku_id
),sale_attr as (
    select sku_id,
           collect_list(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attr_values
    from ods_sku_sale_attr_value_full
    where dt='${dateStr}'
    group by sku_id
)
insert overwrite table dim_sku_full partition (dt='${dateStr}')
select sku.id,
       sku.price,
       sku.sku_name,
       sku.sku_desc,
       sku.weight,
       sku.is_sale,
       sku.spu_id,
       spu.spu_name,
       sku.category3_id,
       c3.name,
       c3.category2_id,
       c2.name,
       c2.category1_id,
       c1.name,
       sku.tm_id,
       tm.tm_name,
       attr.attr_values,
       sale_attr.sale_attr_values,
       sku.create_time
from sku
left join spu on sku.spu_id = spu.id
left join c3 on sku.category3_id = c3.id
left join c2 on c3.category2_id = c2.id
left join c1 on c2.category1_id = c1.id
left join tm on sku.tm_id = tm.id
left join attr on sku.id = attr.sku_id
left join sale_attr on sku.id = sale_attr.sku_id;"
dim_user_zip="set hive.exec.dynamic.partition.mode=nonstrict;
with
old as (
    select id,
           login_name,
           nick_name,
           name,
           phone_num,
           email,
           user_level,
           birthday,
           gender,
           create_time,
           operate_time,
           start_date,
           end_date
    from dim_user_zip
    where dt='9999-99-99'
),i as (
    select data.id,
           data.login_name,
           data.nick_name,
           data.name,
           data.phone_num,
           data.email,
           data.user_level,
           data.birthday,
           data.gender,
           data.create_time,
           data.operate_time,
           date_format(data.create_time,'yyyy-MM-dd') start_date,
           '9999-99-99' end_date
    from ods_user_info_inc
    where dt='${dateStr}' and type='insert'
),u as (
    select data.id,
           data.login_name,
           data.nick_name,
           data.name,
           data.phone_num,
           data.email,
           data.user_level,
           data.birthday,
           data.gender,
           data.create_time,
           data.operate_time,
           date_format(if(data.operate_time is null,data.create_time,data.operate_time),'yyyy-MM-dd') start_date,
           '9999-99-99' end_date
    from ods_user_info_inc
    where dt='${dateStr}' and type='update'
)
insert overwrite table dim_user_zip partition (dt)
select id,
       login_name,
       nick_name,
       name,
       phone_num,
       email,
       user_level,
       birthday,
       gender,
       create_time,
       operate_time,
       start_date,
       if(rn=1,'9999-99-99','${dateStr}') end_date,
       if(rn=1,'9999-99-99','${dateStr}') dt
from (
         select id,
                login_name,
                nick_name,
                name,
                phone_num,
                email,
                user_level,
                birthday,
                gender,
                create_time,
                operate_time,
                start_date,
                end_date,
                row_number() over (partition by id order by start_date desc) rn
         from (
                  select *
                  from old
                  union all
                  select *
                  from i
                  union all
                  select *
                  from u
              ) t1
) t2;"

# 3.执行导数SQL
case $1 in
"all")
	$HIVE_HOME/bin/hive -e "${dim_activity_full}${dim_coupon_full}${dim_province_full}${dim_sku_full}${dim_user_zip}"
;;
"dim_activity_full")
    $HIVE_HOME/bin/hive -e "${dim_activity_full}"
;;
"dim_coupon_full")
    $HIVE_HOME/bin/hive -e "${dim_coupon_full}"
;;
"dim_province_full")
    $HIVE_HOME/bin/hive -e "${dim_province_full}"
;;
"dim_sku_full")
    $HIVE_HOME/bin/hive -e "${dim_sku_full}"
;;
"dim_user_zip")
    $HIVE_HOME/bin/hive -e "${dim_user_zip}"
;;
esac
